package tgc.edu.szxt.demo.service;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import tgc.edu.szxt.demo.custom.BaseService;
import tgc.edu.szxt.demo.entity.Course;
import tgc.edu.szxt.demo.entity.Grade;
import tgc.edu.szxt.demo.repository.GradeRepository;

@Service
public class GradeService extends BaseService<Grade, Integer>{
	
	@Autowired
	private GradeRepository gradeDAO;
	@Resource
	private EntityManager entityManager;
	
	
	public List<Grade> findByStudentId(Integer id) {
		
		return gradeDAO.findByStudentId(id);
	}

	public Grade findByStudentIdAndCourseId(Integer id, Integer kcId) {
		// TODO Auto-generated method stub
		return gradeDAO.findByStudentIdAndCourseId(id,kcId);
	}
	
	
	public List<Course> findDistinctCourseByTbClassId(Integer bjId){
		return gradeDAO.findDistinctCourseByTbClassId(bjId);
	}
	

	public List<Object[]> findByCjb(Integer bjId,List<Course> courses){
		String hql="select t.student.stuNo,t.student.name ";
		for(Course course : courses) {
			hql+=",sum(case when t.course.id="+course.getId()+"then t.grade else 0 end)";
		}
		hql+=",sum(t.grade) as total";
		hql+=" from Grade t";
		hql+=" where t.student.tbClass.id="+bjId;
		hql+=" group by t.student.id";
		hql+=" order by total desc";
		Query query=entityManager.createQuery(hql);
		entityManager.close();
		return query.getResultList();	
	}
	

}
